package lsy.Dao;

import lsy.tools.PageInfo;
import lsy.tools.PropertiesUtils;
import lsy.entity.Teacher;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;
import org.apache.commons.lang3.StringUtils;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class TeacherDao {
    public void add(Teacher teacher) throws SQLException {
        QueryRunner queryRunner= new QueryRunner(PropertiesUtils.getDataSource());
        String sql = "insert into teacher(tid,teaName,teaUsername,teaPwd) values(?,?,?,?)";
        queryRunner.update(sql,teacher.getTid(),teacher.getTeaName(),teacher.getTeaUsername(),teacher.getTeaPwd());
    }
    public void delete(Integer tid) throws SQLException{
        QueryRunner queryRunner= new QueryRunner(PropertiesUtils.getDataSource());
        String sql = "delete from teacher where tid=?";
        queryRunner.update(sql,tid);
    }
    public void update(Teacher teacher) throws SQLException{
        QueryRunner queryRunner= new QueryRunner(PropertiesUtils.getDataSource());
        String sql = "update teacher set teaUsername=?,teaName=? where tid=?";
        queryRunner.update(sql,teacher.getTeaUsername(),teacher.getTeaName(),teacher.getTid());
    }
    public PageInfo<Teacher> list(Teacher teacher, PageInfo<Teacher> pageInfo) throws SQLException {
        QueryRunner queryRunner = new QueryRunner(PropertiesUtils.getDataSource());
        String _sql = "";
        List<Object> _list = new ArrayList<Object>();
        if(teacher != null && teacher.getTid() != null) {
            _sql += " and tid = ?";
            _list.add(teacher.getTid());
        }
        if(teacher != null && StringUtils.isNoneBlank(teacher.getTeaName())) {
            _sql += " and teaName like ?";
            _list.add("%"+teacher.getTeaName()+"%");
        }
        if(teacher != null && StringUtils.isNoneBlank(teacher.getTeaUsername())) {
            _sql += " and teaUsername like ?";
            _list.add("%"+teacher.getTeaUsername()+"%");
        }
        //_list转数组
        Object[] arr = new Object[_list.size()];
        for (int i=0;i<_list.size();i++) {
            arr[i] = _list.get(i);
        }
        String sql = "select * from teacher where 1=1 "+_sql+" limit  "+(pageInfo.getPageNo()-1)*pageInfo.getPageSize()+" , "+pageInfo.getPageSize();
        List<Teacher> list = queryRunner.query(sql, new BeanListHandler<>(Teacher.class),arr);
        pageInfo.setList(list);
        pageInfo.setTotalCount(this.count(teacher));
        return pageInfo;
    }


    public Long count(Teacher teacher) throws SQLException {
        QueryRunner queryRunner = new QueryRunner(PropertiesUtils.getDataSource());
        String _sql = "";
        List<Object> _list = new ArrayList<Object>();
        if(teacher != null && teacher.getTid() != null) {
            _sql += " and tid = ?";
            _list.add(teacher.getTid());
        }
        if(teacher != null && StringUtils.isNoneBlank(teacher.getTeaName())) {
            _sql += " and teaName like ?";
            _list.add("%"+teacher.getTeaName()+"%");
        }
        if(teacher != null && StringUtils.isNoneBlank(teacher.getTeaUsername())) {
            _sql += " and teaUsername like ?";
            _list.add("%"+teacher.getTeaUsername()+"%");
        }
        //_list转数组
        Object[] arr = new Object[_list.size()];
        for (int i=0;i<_list.size();i++) {
            arr[i] = _list.get(i);
        }
        String sql = "select count(*) from teacher where 1=1 "+_sql;
        Long count = (Long)queryRunner.query(sql, new ScalarHandler(),arr);
        return count;
    }

    public Teacher findById(Integer tid) throws SQLException {
        QueryRunner queryRunner= new QueryRunner(PropertiesUtils.getDataSource());
        String sql = "select * from teacher where tid=?";
        Teacher teacher =queryRunner.query(sql,new BeanHandler<>(Teacher.class),tid);
        return teacher;
    }
    public Teacher login(String teaUsername,String teaPwd) throws SQLException {
        QueryRunner queryRunner= new QueryRunner(PropertiesUtils.getDataSource());
        String sql = "select * from teacher where teaUsername=? and teaPwd=?";
        Teacher teacher =queryRunner.query(sql,new BeanHandler<>(Teacher.class),teaUsername,teaPwd);
        return teacher;
    }
}
